package re.kitri.common.board;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import re.kitri.common.DBConnect.DBConnManager;

public class BoardDAO {
	/**
	 * 占쌉쏙옙占쏙옙 占쏙옙 占쌉시뱄옙 占쏙옙 占쏙옙占쏙옙占쏙옙占쏙옙
	 * @param sch
	 * @return
	 */
	public int totalListCount(BoardDTO sch) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DBConnManager db = new DBConnManager();
		StringBuffer sql= new StringBuffer();
		int totalCount = 0;
	
		sql.append("select count(1) as cnt from board ");
		if(!sch.getSchtxt().equals("")) {
			if(sch.getSchgubun().equals("board_title")) {
				sql.append(" where board_title like ? ");
			} else if(sch.getSchgubun().equals("board_id")) {
				sql.append(" where board_id like ? ");
			}
		}
	 
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql.toString());
			if(!sch.getSchtxt().equals("")) {
				pstmt.setString(1, "%"+sch.getSchtxt()+"%");
			}
			rs = pstmt.executeQuery();
			while(rs.next()) {
				totalCount = rs.getInt("cnt");
			}
		} catch(SQLException e) {
			e.printStackTrace();

		} finally {
			db.closeDB(conn, pstmt, rs);
		}
		return totalCount;
	}
	
	
	/** 
	 * 占쌉쏙옙占쏙옙 占쏙옙占�占쏙옙占쏙옙占쏙옙占쏙옙
	 * @param sch
	 * @return
	 */
	public ArrayList boardList(BoardDTO sch) {
		ArrayList list  = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DBConnManager db = new DBConnManager();
		StringBuffer sql = new StringBuffer();
		sql.append("select rnum, board_seq, board_title, board_regdate,board_id,vcount");
		sql.append(" from (");
		sql.append("   select rownum rnum, board_seq, board_title, board_regdate,board_id,vcount ");
		sql.append("   from (");
		
		sql.append("     select board_seq,board_title,to_char(board_regdate,'YYYY/MM/DD') as board_regdate,board_id,vcount from board ");
		if(!sch.getSchtxt().equals("")) {
			if(sch.getSchgubun().equals("board_title")) {
				sql.append(" where board_title like ? ");
			} else if(sch.getSchgubun().equals("board_id")) {
				sql.append(" where board_id like ? ");
			}
		}
		sql.append("  order by board_seq desc  ");
		sql.append("    ) ");
		sql.append("  ) ");
		sql.append("  where rnum between ? and ? ");

		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql.toString());
			if(!sch.getSchtxt().equals("")) {
				pstmt.setString(1, "%"+sch.getSchtxt()+"%");
				pstmt.setInt(2, sch.getStartSeq());
				pstmt.setInt(3, sch.getEndSeq());
			} else {
				pstmt.setInt(1, sch.getStartSeq());
				pstmt.setInt(2, sch.getEndSeq());
			}
			rs = pstmt.executeQuery();
			while(rs.next()) {
				BoardDTO dto = new BoardDTO();
				
				dto.setBoard_seq(rs.getInt("board_seq"));
				dto.setBoard_id(rs.getString("board_id"));
				dto.setBoard_title(rs.getString("board_title"));
				dto.setBoard_regdate(rs.getString("board_regdate"));
				dto.setVcount(rs.getInt("vcount"));
				list.add(dto);
			}
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeDB(conn, pstmt, rs);
		}
		return list;
	}	
	/**
	 * 占쌉쏙옙占쏙옙 占쏙옙회占쏙옙 占쏙옙占쏙옙
	 * @param dto
	 * @return
	 */
	public int vcountUpdate(BoardDTO dto) {
		int res  = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update board" +
				" set vcount = vcount+1 " +
				" where board_seq=?";
		DBConnManager db = new DBConnManager();
		
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getBoard_seq());
			res = pstmt.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeDB(conn, pstmt);
		}
		return res;
	}
	
	/**
	 * 占쏙옙占싸울옙 占쌉쏙옙占쏙옙
	 * @param dto
	 * @return
	 */
	public ArrayList MainList(BoardDTO dto) {
		ArrayList list  = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DBConnManager db = new DBConnManager();
		StringBuffer sql = new StringBuffer();
		sql.append("select rnum, board_seq, board_title, board_regdate,board_id,vcount");
		sql.append(" from (");
		sql.append("   select rownum rnum, board_seq, board_title, board_regdate,board_id,vcount ");
		sql.append("   from (");
		
		sql.append("     select board_seq,board_title,to_char(board_regdate,'YYYY/MM/DD') as board_regdate,board_id,vcount from board ");
		sql.append("  order by board_seq desc  ");
		sql.append("    ) ");
		sql.append("  ) ");
		sql.append("  where rnum between 1 and 5");
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			
			while(rs.next()) {	
				BoardDTO boardListDTO = new BoardDTO();
				boardListDTO.setBoard_seq(rs.getInt("board_seq"));
				boardListDTO.setBoard_id(rs.getString("board_id"));
				boardListDTO.setBoard_title(rs.getString("board_title"));
				boardListDTO.setBoard_regdate(rs.getString("board_regdate"));
				boardListDTO.setVcount(rs.getInt("vcount"));
				
				System.out.println(dto.getBoard_seq()+"send");
				list.add(boardListDTO);
			}
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeDB(conn, pstmt, rs);
		}
		return list;
	}	
	
	/**
	 * 占쌉쏙옙占쏙옙 占쏢세븝옙占쏙옙
	 * @param dto
	 * @return
	 */
	public BoardDTO boardView(BoardDTO dto) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DBConnManager db = new DBConnManager();
		String sql= "select board_seq, board_regdate,board_title,board_contents,to_char(board_regdate,'YYYY/MM/DD')as board_regdate,board_id from board where board_seq =?";
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getBoard_seq());
			rs = pstmt.executeQuery();
			if(rs.next()) {
				dto.setBoard_seq(rs.getInt("board_seq"));
				dto.setBoard_id(rs.getString("board_id"));
				dto.setBoard_title(rs.getString("board_title"));
				dto.setBoard_contents(rs.getString("board_contents"));
				dto.setBoard_regdate(rs.getString("board_regdate"));
			}
		} catch(SQLException e) {
			e.printStackTrace();

		} finally {
			db.closeDB(conn, pstmt, rs);
		}
		return dto;
	}
	/**
	 * 占쌉쏙옙占쏙옙 占쌉뤄옙
	 * @param dto
	 * @return
	 */
	public int boardInsert(BoardDTO dto) {
		int res  = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int preSeq = 0;
		String seqSQL = "select board_seq.nextval as preseq from dual";
		
		String sql = "insert into board(board_title,board_contents,board_seq," +
				"board_regdate,member_seq,board_id,vcount) values(?,?,?,sysdate,?,?,?)";
		DBConnManager db = new DBConnManager();
		
		System.out.println(dto.getBoard_title());
		System.out.println(dto.getBoard_contents());
		System.out.println(dto.getBoard_id());

		try {
			conn = db.connDB();
			//--------------nextval.sql----
			pstmt = conn.prepareStatement(seqSQL);
			rs = pstmt.executeQuery();
			if(rs.next())
				preSeq = rs.getInt("preseq");
			//------------------------------
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getBoard_title());
			pstmt.setString(2, dto.getBoard_contents());
			pstmt.setInt(3, preSeq);
			pstmt.setInt(4, dto.getMember_seq());
			pstmt.setString(5, dto.getBoard_id());
			pstmt.setInt(6, dto.getVcount());
			res = pstmt.executeUpdate();
			conn.commit();
			
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeDB(conn, pstmt, rs);
		}
		return preSeq;
	}
	
	/**
	 * 占쌉쏙옙占쏙옙 占쏙옙占쏙옙
	 * @param dto
	 * @return
	 */
	public int boardUpdate(BoardDTO dto) {
		int res  = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update board set board_title=?,board_contents=? where board_seq=?";
		DBConnManager db = new DBConnManager();

		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getBoard_title());
			pstmt.setString(2, dto.getBoard_contents());
			pstmt.setInt(3, dto.getBoard_seq());
			res = pstmt.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeDB(conn, pstmt);
		}
		return res;
	}
	/**
	 * 占쌉쏙옙占쏙옙 占쏙옙占쏙옙
	 * @param dto
	 * @return
	 */
	public int boardDelete(BoardDTO dto){
		int res  = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from board where board_seq=?";
		DBConnManager db = new DBConnManager();

		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getBoard_seq());
			res = pstmt.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeDB(conn, pstmt);
		}
		return res;
	}	
	
	//-------------------- [占썰변占쏙옙占� --------------
	/**
	 * 占썰변占쏙옙占�占쏙옙占쏙옙占쏙옙占쏙옙
	 * @param seq
	 * @return
	 */
	public ArrayList boardReplyList(BoardDTO sch) {
		ArrayList list  = new ArrayList();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DBConnManager db = new DBConnManager();
		StringBuffer sql = new StringBuffer();
		sql.append("  select reply_seq,board_seq, board_id, reply_contents, to_char(reply_regdate, 'yyyy/mm/dd') as reply_regdate ");		
		sql.append("  from reply ");
		sql.append(" where board_seq=? order by reply_seq asc  ");
		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1, sch.getBoard_seq());
			rs = pstmt.executeQuery();
			while(rs.next()) {
				BoardDTO dto = new BoardDTO();
				dto.setReply_seq(rs.getInt("reply_seq"));
				dto.setBoard_seq(rs.getInt("board_seq"));
				dto.setBoard_id(rs.getString("board_id"));
				dto.setReply_contents(rs.getString("reply_contents"));
				dto.setReply_regdate(rs.getString("reply_regdate"));	
				list.add(dto);

			}
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeDB(conn, pstmt, rs);
		}
		return list;
	}	
	/**
	 * 占썰변 占쌉뤄옙
	 * @param seq
	 * @return
	 */
	public int boardReplyInsert(BoardDTO dto) {
		int res  = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "insert into reply(reply_seq,board_seq,reply_contents,reply_regdate,board_id) values (reply_seq.nextval, ?, ?, sysdate,?)";
		DBConnManager db = new DBConnManager();

		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getBoard_seq());
			pstmt.setString(2, dto.getReply_contents());
			pstmt.setString(3, dto.getBoard_id());
			res = pstmt.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeDB(conn, pstmt);
		}
		return res;
	}
	/**
	 * 占썰변 占쏙옙占쏙옙
	 * @param seq
	 * @return
	 */
	public int boardReplyDelete(BoardDTO dto) {
		int res  = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from reply where reply_seq=?";
		DBConnManager db = new DBConnManager();

		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getReply_seq());
			res = pstmt.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeDB(conn, pstmt);
		}
		return res;
	}	
	public int boardReplyAllDelete(BoardDTO dto) {
		int res  = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from reply where board_seq=?";
		DBConnManager db = new DBConnManager();

		try {
			conn = db.connDB();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getBoard_seq());
			res = pstmt.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeDB(conn, pstmt);
		}
		return res;
	}	

}

